package net.web.dao;

import net.web.common.utils.JdbcUtils;
import net.web.common.utils.PageInfo;
import net.web.entity.Blog;
import net.web.entity.Vo.BlogVo;

import java.util.ArrayList;
import java.util.List;

public class BlogDao {

    public PageInfo<BlogVo> pagelist(int pageno, int pagesize, Blog blog) {
        StringBuilder sql = new StringBuilder(
                "select b.id, b.title, b.content, b.excerpt, b.coverimage, b.classid, b.authorid, " +
                        "b.isfeatured, b.iscommentable, b.viewcount, b.likecount, b.collectioncount, " +
                        "b.metatitle, b.metadescription, b.metakeywords, b.publishedat, b.createdat, b.updatedat, " +
                        "c.name classname, u.username authorname " +
                        "from blog b " +
                        "left join class c on b.classid = c.id " +
                        "left join user u on b.authorid = u.id " +
                        "where b.flag = 1 "
        );
        List<Object> params = new ArrayList<>();

        if (blog.getTitle() != null && !blog.getTitle().isEmpty()) {
            sql.append(" and b.title like ? ");
            params.add("%" + blog.getTitle() + "%");
        }
        if (blog.getClassid() != 0) {
            sql.append(" and b.classid = ? ");
            params.add(blog.getClassid());
        }
        if (blog.getAuthorid() != 0) {
            sql.append(" and b.authorid = ? ");
            params.add(blog.getAuthorid());
        }
        if(blog.getAuthorname()!= null && !blog.getAuthorname().isEmpty()){
            sql.append(" and u.username like ? ");
            params.add("%" + blog.getAuthorname() + "%");
        }
        if (blog.getIsfeatured() != -1) {
            sql.append(" and b.isfeatured = ? ");
            params.add(blog.getIsfeatured());
        }
        if(blog.getIscommentable() != null){
            sql.append(" and b.iscommentable = ? ");
            params.add(blog.getIscommentable());
        }


        sql.append(" order by b.isfeatured desc, b.id ");
        sql.append(" limit ?, ? ");
        params.add((pageno - 1) * pagesize);
        params.add(pagesize);

        List<BlogVo> list = JdbcUtils.executeQuery(BlogVo.class, sql.toString(), params.toArray());

        StringBuilder countSql = new StringBuilder(
                "select count(*) from blog b " +
                        "left join user u on b.authorid = u.id " +
                        "where b.flag = 1 "
        );
        List<Object> countParams = new ArrayList<>();

        if (blog.getTitle() != null && !blog.getTitle().isEmpty()) {
            countSql.append(" and b.title like ? ");
            countParams.add("%" + blog.getTitle() + "%");
        }
        if (blog.getClassid() != 0) {
            countSql.append(" and b.classid = ? ");
            countParams.add(blog.getClassid());
        }
        if (blog.getAuthorid() != 0) {
            countSql.append(" and b.authorid = ? ");
            countParams.add(blog.getAuthorid());
        }
        if (blog.getIsfeatured() != -1) {
            countSql.append(" and b.isfeatured = ? ");
            countParams.add(blog.getIsfeatured());
        }
        if (blog.getAuthorname() != null && !blog.getAuthorname().isEmpty()) {
            countSql.append(" and u.username like ? ");
            countParams.add("%" + blog.getAuthorname() + "%");
        }

        long total = JdbcUtils.executeQueryOneValue(countSql.toString(), countParams.toArray());

        return new PageInfo<>(pageno, pagesize, (int) total, list);
    }


    public boolean insert(Blog blog) {
        String sql =
                "insert into blog(title, content, excerpt, coverimage, classid, authorid, " +
                        "isfeatured, iscommentable, metatitle, metadescription, metakeywords, publishedat, flag) " +
                        "values (?,?,?,?,?,?,?,?,?,?,?,?,1)";
        int rows = JdbcUtils.executeUpdate(sql,
                blog.getTitle(),
                blog.getContent(),
                blog.getExcerpt(),
                blog.getCoverimage(),
                blog.getClassid(),
                blog.getAuthorid(),
                blog.getIsfeatured(),
                blog.getIscommentable(),
                blog.getMetatitle(),
                blog.getMetadescription(),
                blog.getMetakeywords(),
                blog.getPublishedat()
        );
        return rows > 0;
    }


    public boolean delete(int id) {
        String sql = "update blog set flag = 0 where id = ?";
        return JdbcUtils.executeUpdate(sql, id) > 0;
    }


    public boolean update(Blog blog) {
        StringBuilder sql = new StringBuilder("update blog set ");
        List<Object> params = new ArrayList<>();

        if (blog.getTitle() != null && !blog.getTitle().isEmpty()) {
            sql.append("title = ?, "); params.add(blog.getTitle());
        }
        if (blog.getContent() != null && !blog.getContent().isEmpty()) {
            sql.append("content = ?, "); params.add(blog.getContent());
        }
        if (blog.getExcerpt() != null && !blog.getExcerpt().isEmpty()) {
            sql.append("excerpt = ?, "); params.add(blog.getExcerpt());
        }
        if (blog.getCoverimage() != null && !blog.getCoverimage().isEmpty()) {
            sql.append("coverimage = ?, "); params.add(blog.getCoverimage());
        }
        if (blog.getClassid() != 0) {
            sql.append("classid = ?, "); params.add(blog.getClassid());
        }
        if (blog.getAuthorid() != 0) {
            sql.append("authorid = ?, "); params.add(blog.getAuthorid());
        }
        // 下面两个 int 字段按业务需要决定 0 是否合法
        if (blog.getIsfeatured() == 0 || blog.getIsfeatured() == 1) {
            sql.append("isfeatured = ?, "); params.add(blog.getIsfeatured());
        }
        if (blog.getIscommentable() == 0 || blog.getIscommentable() == 1) {
            sql.append("iscommentable = ?, "); params.add(blog.getIscommentable());
        }
        if (blog.getMetatitle() != null) {
            sql.append("metatitle = ?, "); params.add(blog.getMetatitle());
        }
        if (blog.getMetadescription() != null) {
            sql.append("metadescription = ?, "); params.add(blog.getMetadescription());
        }
        if (blog.getMetakeywords() != null) {
            sql.append("metakeywords = ?, "); params.add(blog.getMetakeywords());
        }
        if (blog.getPublishedat() != null) {
            sql.append("publishedat = ?, "); params.add(blog.getPublishedat());
        }

        if (params.isEmpty()) return false;

        sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" where id = ?");
        params.add(blog.getId());

        return JdbcUtils.executeUpdate(sql.toString(), params.toArray()) > 0;
    }

    public Blog getBlogById(int id) {
        String sql = "select * from blog where id = ? and flag = 1";
        List<Blog> list = JdbcUtils.executeQuery(Blog.class, sql, id);
        return list.isEmpty() ? null : list.get(0);
    }

    public Integer getAuthorIdByBlogId(int blogId) {
        String sql = "SELECT authorid FROM blog WHERE id = ? AND flag = 1";
        Object result = JdbcUtils.executeQueryOneValue(sql, blogId);
        return result != null ? ((Number) result).intValue() : null;
    }
}